package com.exedosoft.plat.gene;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.exedosoft.plat.DAOUtil;
import com.exedosoft.plat.bo.DOBO;
import com.exedosoft.plat.bo.DOBOProperty;
import com.exedosoft.plat.bo.DODataSource;
import com.exedosoft.plat.bo.DOParameter;
import com.exedosoft.plat.gene.jquery.SqlCol;
import com.exedosoft.plat.util.StringUtil;
import com.exedosoft.plat.util.id.UUIDHex;

public class ATableForwarderImp implements ATableForwarder {

	private static Log log = LogFactory.getLog(ATableForwarderImp.class);

	private String table;

	private String dataSourceUid;

	private String busiPackageUid;

	private String BOUid;

	
	private boolean isPostgres = false;
	
	public ATableForwarderImp(String aDataSourceUid) {

		this.dataSourceUid = aDataSourceUid;
	}
	

	public ATableForwarderImp(String aTable, String aDataSource,
			String aBusiPackage) {
		table = aTable;
		dataSourceUid = aDataSource;
		busiPackageUid = aBusiPackage;
	}

	public Connection getConnection() {

		if (this.dataSourceUid != null) {

			try {
				DODataSource dds = (DODataSource) DAOUtil.INSTANCE().getByObjUid(
						DODataSource.class, this.dataSourceUid);
				
				if(dds.isPostgreSQL()){
					isPostgres = true;
				}

				return dds.getConnection();
			} catch (Exception e) {
				e.printStackTrace();
				return null;
			}
		} else {
			return DODataSource.getDefaultCon();
		}

	}

	public void forwardDOBO() {

		Connection con = null;
		try {
			con = DODataSource.getDefaultCon();
			String boUid = this.getDOBOUid(table);
			log.info("当前添加的业务对象名称:" + table);
			log.info("当前添加的业务对象ID:" + boUid);

			if (boUid == null) { // ////如果配置中没有当前view 的配置
				String insertTable = "insert into do_bo(objUid,name,l10n,sqlstr,dataSourceUid,bpUID, keycol,iscache,type)  values(?,?,?,?,?,?,'objuid',1,1)";

				PreparedStatement pstmt = con.prepareStatement(insertTable);
				boUid = UUIDHex.getInstance().generate();
				pstmt.setString(1, boUid);
				pstmt.setString(2, StringUtil.getDotName(table));
				pstmt.setString(3, StringUtil.getDotName(table));
				pstmt.setString(4, table);
				pstmt.setString(5, dataSourceUid);
				pstmt.setString(6, busiPackageUid);
				pstmt.executeUpdate();
			}
			BOUid = boUid;

		} catch (SQLException ex1) {
			ex1.printStackTrace();
		} finally {
			try {
				if (!con.isClosed()) {
					con.close();
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}

	}

	public void forwardProperty() {

		Connection con = null;
		try {
			con = DODataSource.getDefaultCon();
			Statement stmt = con.createStatement();
			String boUid = this.getDOBOUid(table);
			if (boUid == null) {
				log.warn("boUid未制定：" + table + "可能未配置");
				return;
			}
			log.info("");
			log.info("正在处理业务对象:" + table);
			log.info("正在产生业务属性添加语句。。。。。");
			int i = 1;
			for (Iterator itCol = this.getCols(table).iterator(); itCol
					.hasNext();) {
				SqlCol sqlCol = (SqlCol) itCol.next();
				if (!isModify(sqlCol.getName(), boUid)) { // ///没有被修改过的字段才可以被覆盖
					StringBuffer buffer = new StringBuffer();
					String isNull = "isNull";
					if(isPostgres){
						isNull = "\"isnull\"";
					}
					buffer
							.append(
									"insert into DO_BO_Property(objuid,boUid,col_name,prop_name,l10n,dbType,")
							.append(isNull)
							.append(",isModify,order_num,dbSize,readonly) values(")
							.append("'").append(
									UUIDHex.getInstance().generate()).append(
									"',") // //viewUID
							.append("'").append(boUid).append("',") // /colname
							.append("'").append(sqlCol.getName()).append("',") // /colname
							.append("'").append(sqlCol.getName()).append("',") // /propname
							.append("'").append(sqlCol.getName()).append("',") // /l10n
							.append(sqlCol.getDataType()).append(","); // /datatype

					if (sqlCol.isAllowedNull()) {
						buffer.append("'1'");
					} else {
						buffer.append("'0'");
					} // /////////////////////////////isNull
					buffer.append(",").append("'0'"); // ///isModify,指修改状态，现在是未修改，是由系统生成的
					buffer.append(",").append(i * 5); // ///order numbber
					buffer.append(",").append(sqlCol.getSize()); // ///readonly
					buffer.append(",").append("'0'"); // ///readonly
					buffer.append(")");
					log.info(buffer);
					stmt.addBatch(buffer.toString());
				}
				i++;
			} // ///添加每个表字段的信息
			log.info("正在执行添加操作。。。。。");
			stmt.executeBatch();
			log.info("操作完成。");
		} catch (SQLException ex) {
			try {
				con.rollback();
			} catch (SQLException ex2) {
				ex2.printStackTrace();
			}
			ex.printStackTrace();
		} finally {
			try {
				if (!con.isClosed()) {
					con.close();
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}

	}

	public void forwardParameter() {
		Connection con = null;
		try {
			con = DODataSource.getDefaultCon();
			con.setAutoCommit(false);
			String seleProps = "SELECT property.* from do_bo_property property, do_bo db where property.boUid = db.objuid and db.name = ?";
			log.info("");
			log.info("正在处理参数转化。。。。。");

			PreparedStatement stmt1 = con.prepareStatement(seleProps);
			stmt1.setString(1, StringUtil.getDotName(table));

			Statement stmt2 = con.createStatement();
			ResultSet prop = stmt1.executeQuery();

			while (prop.next()) {

				if (this.getParaUidByProp(prop.getString("objuid")) == null) {

					int paraType = DOParameter.TYPE_FORM;
					if (prop.getString("col_name") != null) {
						if ("objuid".equals(prop.getString("col_name")
								.toLowerCase())) {
							paraType = DOParameter.TYPE_KEY;
						}
					}

					insertParameterSql(prop, paraType, stmt2);
					if (prop.getString("col_name") != null) {
						if ("objuid".equals(prop.getString("col_name")
								.toLowerCase())) {
							paraType = DOParameter.TYPE_CURRENT;
							insertParameterSql(prop, paraType, stmt2);
						}
					}
				}
			}

			prop.close();
			stmt1.close();
			stmt2.executeBatch();
			con.commit();
			log.info("操作完成。");
		} catch (SQLException ex) {
			try {
				con.rollback();
			} catch (SQLException ex2) {
				ex2.printStackTrace();
			}
			ex.printStackTrace();
		} finally {
			try {
				if (!con.isClosed()) {
					con.close();
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}

	}

	public void forwardService() {

		this.insertService(table, 1);
		this.insertService(table, 2);
		this.insertService(table, 3);
		this.insertService(table, 4);
		this.insertService(table, 5);

	}

	public void forwardRule() {

		Connection con = null;
		try {
			con = DODataSource.getDefaultCon();
			con.setAutoCommit(false);
			String aTableServices = "SELECT service.* FROM do_service  service ,do_bo db where service.bouid = db.objuid and db.name = ? ";
			log.info("");
			log.info("正在处理参数转化。。。。。");

			PreparedStatement stmtService = con
					.prepareStatement(aTableServices);
			stmtService.setString(1, StringUtil.getDotName(table));

			Statement stmtRule = con.createStatement();
			ResultSet services = stmtService.executeQuery();

			while (services.next()) {

				int paraType = DOParameter.TYPE_FORM;
				insertRuleSql(services, paraType, stmtRule);
			}

			services.close();
			stmtService.close();
			stmtRule.executeBatch();
			con.commit();
			log.info("操作完成。");
		} catch (SQLException ex) {
			try {
				con.rollback();
			} catch (SQLException ex2) {
				ex2.printStackTrace();
			}
			ex.printStackTrace();
		} finally {
			try {
				if (!con.isClosed()) {
					con.close();
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}

	}

	/**
	 * InsertService 利用hibernate的api或用exedo本身api来写
	 * 
	 * @param table
	 * @param type
	 *            
	 */
	private void insertService(String table, int type) {

		Connection con = null;

		log.info("");
		log.info("正在生成" + table + "的服务。。。。。。。");

		try {
			con = DODataSource.getDefaultCon();// /获取缺省的数据库连接
			con.setAutoCommit(false);
			Statement stmt = con.createStatement();// //deal service statement
			Statement stmt2 = con.createStatement();// ////////deal parameter
			// statement
			String serviceUid = UUIDHex.getInstance().generate();// ////要生成的servcie
			// uuid

			DOBO bo = DOBO.getDOBOByName(StringUtil.getDotName(table));// ////表对应的业务对象
			List props = new ArrayList();

			StringBuffer mainSql = null;

			String name = "";
			String l10n = "";
			String prefix = StringUtil.getDotName(table);

			Boolean isNew = null;

			switch (type) {
			case 1:
				name = prefix + ".insert";
				l10n = name;
				props = bo.retrieveProperties();
				mainSql = getInsertSql(props, table);
				isNew = Boolean.TRUE;
				break;
			case 2:
				name = prefix + ".update";
				l10n = name;
				props = bo.retrieveProperties();
				mainSql = this.getModiSql(props, table);
				isNew = Boolean.FALSE;
				break;

			case 3:
				DOBOProperty property = DOBOProperty.getDOBOPropertyByName(bo.getName(),
						"objuid");
				System.out.println("BOBOBO::::::" + bo);
				System.out.println("Property::::::" + property);

				if (property == null) {
					return;
				}
				name = prefix + ".delete";
				l10n = name;
				props.add(property);
				mainSql = new StringBuffer("delete from ").append(table)
						.append(" where objuid = ?");
				break;
			case 4:
				property = DOBOProperty.getDOBOPropertyByName(bo.getName(),"objuid");
				if (property == null) {
					return;
				}
				name = prefix + ".browse";
				l10n = name;
				props.add(property);
				mainSql = new StringBuffer("select * from ").append(table)
						.append(" where objuid = ?");
				break;
			case 5:
				name = prefix + ".list";
				l10n = name;
				mainSql = new StringBuffer("select * from ").append(table);
			}

			// ///////根据props 定位parameter ，然后service 和 parameter 进行关联
			this.setParaLinkBatch(props, stmt2, serviceUid, isNew);

			StringBuffer aSql = new StringBuffer(
					"insert into DO_Service(objuid,l10n,name,bouid,mainSql) values(")
					.append("'").append(serviceUid).append("','").append(l10n)
					.append("','").append(name).append("','").append(
							this.getDOBOUid(table)).append("','").append(
							mainSql).append("')");

			log.info("Servcice's Sql:" + aSql.toString());
			stmt.executeUpdate(aSql.toString());
			stmt2.executeBatch();
			con.commit();
		} catch (SQLException ex) {
			try {
				con.rollback();
			} catch (SQLException ex2) {
				ex2.printStackTrace();
			}
			ex.printStackTrace();
		} finally {
			try {
				if (!con.isClosed()) {
					con.close();
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}

	}

	private StringBuffer getInsertSql(List allProps, String table) {

		StringBuffer insertSql = new StringBuffer();
		insertSql.append("insert into ").append(table).append("(");
		log.info("正在生成服务和参数关联SQL:");
		for (Iterator itCol = allProps.iterator(); itCol.hasNext();) {
			DOBOProperty property = (DOBOProperty) itCol.next();
			insertSql.append(property.getColName()); // /colname
			if (itCol.hasNext()) {
				insertSql.append(",");
			} else {
				insertSql.append(")");
			}
		}
		insertSql.append(" values(");
		for (int i = 0; i < allProps.size(); i++) {
			insertSql.append("?");
			if (i < allProps.size() - 1) {
				insertSql.append(",");
			} else {
				insertSql.append(")");
			}

		}
		return insertSql;
	}

	private StringBuffer getModiSql(List allProps, String table) {

		StringBuffer modiSql = new StringBuffer();
		modiSql.append("update ").append(table).append(" set ");
		log.info("正在生成服务和参数关联SQL:");
		for (Iterator itCol = allProps.iterator(); itCol.hasNext();) {
			DOBOProperty property = (DOBOProperty) itCol.next();

			if (!"objuid".equals(property.getColName().toLowerCase())) {

				modiSql.append(property.getColName()); // /colname
				modiSql.append("=?");
				if (itCol.hasNext()) {
					modiSql.append(",");
				} else {
					modiSql.append(" where objuid = ? ");
				}
			}
		}
		return modiSql;
	}

	private void setParaLinkBatch(List allProps, Statement stmt2,
			String serviceUid, Boolean isNew) throws SQLException {

		int order = 1;
		String modiParaUid = null;
		for (Iterator itCol = allProps.iterator(); itCol.hasNext();) {
			DOBOProperty property = (DOBOProperty) itCol.next();

			if ("objuid".equals(property.getColName()) && (isNew == null)) {// ///察看情况

				String paraUid = this.getParaUidByProp(property.getObjUid(),
						DOParameter.TYPE_CURRENT);
				addParaRelationStr(stmt2, serviceUid, order, paraUid);

			} else if ("objuid".equals(property.getColName())
					&& (isNew != null) && !isNew.booleanValue()) {// /修改情况
				// /////////这句话的意思是放到最后执行
				modiParaUid = this.getParaUidByProp(property.getObjUid(),
						DOParameter.TYPE_CURRENT);
				continue;
			} else if ("objuid".equals(property.getColName())
					&& (isNew != null) && isNew.booleanValue()) {// /新增的情况
				String paraUid = this.getParaUidByProp(property.getObjUid(),
						DOParameter.TYPE_KEY);
				addParaRelationStr(stmt2, serviceUid, order, paraUid);
			} else {

				String paraUid = this.getParaUidByProp(property.getObjUid(),
						DOParameter.TYPE_FORM);
				addParaRelationStr(stmt2, serviceUid, order, paraUid);

			}
			order++;
		}
		if (modiParaUid != null) {
			addParaRelationStr(stmt2, serviceUid, order++, modiParaUid);
		}

	}

	/**
	 * @param stmt2
	 * @param serviceUid
	 * @param order
	 * @param paraUid
	 * @throws SQLException
	 */
	private void addParaRelationStr(Statement stmt2, String serviceUid,
			int order, String paraUid) throws SQLException {
		StringBuffer paraRelationStr = new StringBuffer(
				"insert into do_parameter_service(objuid,serviceuid,parameteruid,order_num) values('")
				.append(UUIDHex.getInstance().generate()).append("','").append(
						serviceUid).append("','").append(paraUid).append("',")
				.append(order * 5).append(")");
		log.info("关联的SQL语句:" + paraRelationStr);
		stmt2.addBatch(paraRelationStr.toString());
	}

	/**
	 * @param prop
	 * @param paraType
	 * @return
	 * @throws SQLException
	 */
	private void insertParameterSql(ResultSet prop, int paraType,
			Statement stmt2) throws SQLException {

		String colName = prop.getString("col_name");
		if (paraType == DOParameter.TYPE_CURRENT) {
			colName = this.table + "-currunt";

		}

		StringBuffer aSql = new StringBuffer(
				"insert into do_parameter(objuid,name,l10n,type,paraBoUid,paraPropertyUid) values(")
				.append("'").append(UUIDHex.getInstance().generate()).append(
						"'").append(",'").append(colName).append("'").append(
						",'").append(colName).append("',").append(paraType)
				.append(",'").append(prop.getString("bouid")).append("'")
				.append(",'").append(prop.getString("objuid")).append("')");
		log.info("添加参数的sql语句:" + aSql);
		stmt2.addBatch(aSql.toString());
	}

	/**
	 * @param prop
	 * @param paraType
	 * @return
	 * @throws SQLException
	 */
	private void insertRuleSql(ResultSet prop, int paraType, Statement stmt2)
			throws SQLException {
		// ?,?,?,?,?,?,?,?,?)9

		StringBuffer aSql = new StringBuffer(
				"insert into do_rule(objuid,name,l10n,condition,consequencescript,salience,serviceuid,onlyrun,bpuid) values(")
				.append("'").append(UUIDHex.getInstance().generate()).append(
						"'").append(",'").append("Rule_").append(
						prop.getString("name")).append("'").append(",'")
				.append("Rule_").append(prop.getString("name")).append("'")
				.append(",'true'").append(",null").append(",null").append(",'")
				.append(prop.getString("objuid")).append("'").append(",1")
				.append(",'").append(this.BOUid).append("')");
		log.info("添加参数的sql语句:" + aSql);
		stmt2.addBatch(aSql.toString());
	}

	/**
	 * 通过sql查询语句，返回viewName对应的objUID
	 * 
	 * @param boName
	 */
	public String getDOBOUid(String boName) {

		Connection con = null;
		try {
			con = DODataSource.getDefaultCon();
			String sql = "select objUID from do_bo where type = '1' and sqlStr = ? ";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, boName);
			ResultSet rs = pstmt.executeQuery();
			String boUid = null;
			if (rs.next()) {
				boUid = rs.getString(1);
			}
			return boUid;
		} catch (SQLException ex1) {
			ex1.printStackTrace();
		} finally {
			try {
				if (!con.isClosed()) {
					con.close();
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
		return null;
	}

	protected boolean isModify(String colName, String boUid) {

		Connection con = null;
		try {
			con = DODataSource.getDefaultCon();
			String sql = "select objUID from do_bo_property where col_name = ? and bouid = ? and isModify = '1' ";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, colName);
			pstmt.setString(2, boUid);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {// //处于修改状态，系统初始化时把该字段忽略。
				return true;
			} else {
				sql = "delete from do_bo_property where col_name = ? and bouid = ?";
				PreparedStatement pstmtDete = con.prepareStatement(sql);
				pstmtDete.setString(1, colName);
				pstmtDete.setString(2, boUid);
				pstmtDete.executeUpdate();
				return false;
			}

		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			try {
				if (!con.isClosed()) {
					con.close();
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}
		return false;
	}

	public String getParaUidByProp(String propertyUid, int type) {

		String ret = null;
		Connection con = null;
		try {
			con = DODataSource.getDefaultCon();
			String sql = "select * from do_parameter   where ParaPropertyUid = ? and type = ?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, propertyUid);
			pstmt.setInt(2, type);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {// //找得到记录
				ret = rs.getString("objUid");
			}
			rs.close();
			pstmt.close();
		} catch (SQLException ex) {
			try {
				con.rollback();
			} catch (SQLException ex2) {
				ex2.printStackTrace();
			}
			ex.printStackTrace();
		} finally {
			try {
				if (!con.isClosed()) {
					con.close();
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}

		return ret;
	}

	public String getParaUidByProp(String propertyUid) {

		String ret = null;
		Connection con = null;
		try {
			con = DODataSource.getDefaultCon();
			String sql = "select * from do_parameter   where ParaPropertyUid = ?";
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, propertyUid);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {// //找得到记录
				ret = rs.getString("objUid");
			}
			rs.close();
			pstmt.close();
		} catch (SQLException ex) {
			try {
				con.rollback();
			} catch (SQLException ex2) {
				ex2.printStackTrace();
			}
			ex.printStackTrace();
		} finally {
			try {
				if (!con.isClosed()) {
					con.close();
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}
		}

		return ret;
	}

	public Collection getCols(String aTable) {

		Collection cc = new ArrayList();
		Connection con = null;
		try {
			con = this.getConnection();
			DatabaseMetaData meta = con.getMetaData();
			System.out.println(con.getCatalog());
			
			DODataSource dds = (DODataSource) DAOUtil.INSTANCE().getByObjUid(
					DODataSource.class, this.dataSourceUid);

			String schema = null;
			
			if (dds.isOracle()) {
				schema = dds.getUserName().trim().toUpperCase();
			}
			ResultSet rs = meta.getColumns(null, schema, aTable, null);

			while (rs.next()) {
				SqlCol qc = new SqlCol();
				qc.setName(rs.getString("COLUMN_NAME").toLowerCase());
				qc.setDataType(rs.getInt("DATA_TYPE"));
				qc.setScale(rs.getInt("DECIMAL_DIGITS"));
				qc.setNullable(rs.getString("IS_NULLABLE"));
				qc.setSize(rs.getInt("COLUMN_SIZE"));
				cc.add(qc);
			}
		} catch (SQLException ex) {
			ex.printStackTrace();
		} finally {
			try {
				if (!con.isClosed()) {
					con.close();
				}
			} catch (SQLException ex1) {
				ex1.printStackTrace();
			}

		}
		return cc;
	}

	public void forwardUI() {
		GeneUICompByTable gt = new GeneUICompByTable(this.table,
				this.BOUid);
		gt.geneConfig();
	}

	public void forwardAll() {

		this.forwardDOBO();
		this.forwardProperty();
		this.forwardParameter();
		this.forwardService();
		this.forwardRule();
		this.forwardUI();

	}

	public static void main(String[] args) {
		
		ATableForwarderImp  ai = new ATableForwarderImp("mysqlexample");
		
		System.out.println("CCCCCCCCCC:::" + ai.getCols("student"));

		// String[] aa = {"-database.0","mydb","-dbname.0","mydb"};
		// org.hsqldb.Server.main(aa);

		// URL url = ATableForwarderImp.class.getResource("abc.xml");
		// String aPath = url.getPath();
		// System.out.println(aPath);
		// String aSubPath = aPath.substring(0,aPath.indexOf("abc.xml"));
		// System.out.println(aSubPath);
		// /url.getPath()

	}
}
